const db = require('../config/database');

class Job {
  static async create({ company, title, position, description, requirements, skills, status = 'open' }) {
    const [result] = await db.execute(
      'INSERT INTO jobs (company, title, position, description, requirements, skills, status) VALUES (?, ?, ?, ?, ?, ?, ?)',
      [company, title, position, description, requirements, skills, status]
    );
    
    // 更新匹配校友的招聘状态
    await this.updateAlumniOpenings(company);
    
    return result.insertId;
  }

  static async updateAlumniOpenings(company) {
    // 查找匹配公司的校友
    const [rows] = await db.execute(
      'SELECT id FROM alumni WHERE company = ?',
      [company]
    );
    
    if (rows.length > 0) {
      // 更新这些校友的 hasOpenings 状态
      await db.execute(
        'UPDATE alumni SET hasOpenings = 1 WHERE company = ?',
        [company]
      );
    }
  }

  static async update(id, { title, position, description, requirements, skills, status }) {
    const [result] = await db.execute(
      'UPDATE jobs SET title = ?, position = ?, description = ?, requirements = ?, skills = ?, status = ? WHERE id = ?',
      [title, position, description, requirements, skills, status, id]
    );
    return result.affectedRows > 0;
  }

  static async getById(id) {
    const [rows] = await db.execute(
      'SELECT * FROM jobs WHERE id = ?',
      [id]
    );
    return rows[0];
  }

  static async getByCompany(company) {
    const [rows] = await db.execute(
      'SELECT * FROM jobs WHERE company = ? AND status = "open"',
      [company]
    );
    return rows;
  }

  static async search({ keywords, skills, hasAlumni }) {
    let query = 'SELECT * FROM jobs WHERE status = "open"';
    const params = [];

    if (keywords) {
      query += ' AND (title LIKE ? OR description LIKE ?)';
      params.push(`%${keywords}%`, `%${keywords}%`);
    }

    if (skills) {
      query += ' AND skills LIKE ?';
      params.push(`%${skills}%`);
    }

    if (hasAlumni !== undefined) {
      query += ' AND has_alumni = ?';
      params.push(hasAlumni);
    }

    const [rows] = await db.execute(query, params);
    return rows;
  }
}

module.exports = Job;
